﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Golos.Helper;
using Golos.Tags.Configuration;

namespace Golos.Tags.TagCache
{
    public partial class Cache
    {
        private void GetTagCacheFromDb()
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(@"SELECT PollTag.QuestionID, Tags.Tag FROM Tags INNER JOIN PollTag ON PollTag.TagID = Tags.TagID", connection);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                            return;
                        while (reader.Read())
                        {
                            Guid questionId = reader.GetGuid(0);
                            string tag = reader.GetString(1);
                            if(!tags.ContainsKey(tag))
                                tags.Add(tag, new List<Guid>());
                            tags[tag].Add(questionId);
                            if(!questionTags.ContainsKey(questionId))
                                questionTags.Add(questionId, new List<string>());
                            if(!questionTags.ContainsValue(questionId, tag))
                                questionTags[questionId].Add(tag);
                        }
                    }
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    throw;
                }
            }
        }

        private bool SaveNewTagInDb(Guid questionId, string newTag, bool uniqTag)
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    Guid tagId = Guid.NewGuid();
                    SqlCommand command;
                    if(uniqTag)
                    {
                        command = new SqlCommand("INSERT INTO [Tags] ([TagID],[Tag]) VALUES (@tagid, @tag)", connection, transaction);
                        command.Parameters.Add("@tagid", SqlDbType.UniqueIdentifier);
                        command.Parameters.Add("@tag", SqlDbType.VarChar);
                        command.Parameters["@tagid"].Value = tagId;
                        command.Parameters["@tag"].Value = newTag;
                        command.ExecuteNonQuery();
                    }
                    else
                        tagId = new Guid(Convert.ToString(new SqlCommand(string.Format("SELECT TOP 1 [TagID] FROM [Tags] WHERE [Tag] = '{0}'", newTag), connection, transaction).ExecuteScalar()));
                    command = new SqlCommand("INSERT INTO [PollTag] ([QuestionId],[TagID]) VALUES (@questionid, @tagid)", connection, transaction);
                    command.Parameters.Add("@tagid", SqlDbType.UniqueIdentifier);
                    command.Parameters.Add("@questionid", SqlDbType.UniqueIdentifier);
                    command.Parameters["@tagid"].Value = tagId;
                    command.Parameters["@questionid"].Value = questionId;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    transaction.Rollback();
                    return false;
                }
            }
        }

        private bool RemoveQuestionTagsInDb(Guid questionId)
        {
            using (SqlConnection connection = new SqlConnection(Settings.ReadFromFile().DbConnectionString))
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    SqlCommand command = new SqlCommand("DELETE FROM [PollTag] WHERE [QuestionId] = @questionid", connection, transaction);
                    command.Parameters.Add("@questionid", SqlDbType.UniqueIdentifier);
                    command.Parameters["@questionid"].Value = questionId;
                    command.ExecuteNonQuery();
                    command = new SqlCommand("DELETE FROM [Tags] WHERE [TagID] not in (SELECT [TagID] FROM [PollTag])", connection, transaction);
                    command.ExecuteNonQuery();
                    transaction.Commit();
                    return true;
                }
                catch (Exception exception)
                {
                    LogClass.WriteErrorEntry(exception.ToString());
                    transaction.Rollback();
                    return false;
                }
            }
        }
    }
}
